﻿Imports System.Text.RegularExpressions
Imports System.Text
Imports MySql.Data.MySqlClient
Imports System.Drawing.Printing
Imports System.IO

Public Class clinica
    Private Sub clinica_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Form1.Show()
    End Sub

    Dim connectionstring As String
    Dim commandtext As String
    'Private mydataset As DataSet
    Dim adapter As MySqlDataAdapter
    Dim adapterSerch As MySqlDataAdapter
    Dim table As DataTable
    Dim conexion As MySqlConnection

    Dim codPac, codHist, codCli As String
    Dim nomPac, tipoAnimal, razPac As String
    Dim motIng, diag, trat As String
    Public nomCli, apeCli, telCli, mailCli, dirCli, codPosCli, ciuCli, paisCli, dniCli As String
    Dim fechIng, fechAlt As String

    Private Sub clinica_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connectionstring = "server=localhost;user id=root;password=root;database=zoo;"
        commandtext = "select m.id as idM, m.nombre as mascotas, m.tipo,m.raza, h.id as idH,h.ingreso,h.alta,h.motivo,h.diagnostico,h.tratamiento,c.dni,c.nombre,c.apellidos,c.direccion,c.telefono,c.ciudad,c.pais,c.codpostal,c.email from cliente c inner join mascota m on c.dni=m.duenio inner join historial h on h.mascota=m.id "
        Try
            adapter = New MySqlDataAdapter(commandtext, connectionstring)
            table = New DataTable
            adapter.Fill(table)

            DataGridView1.DataSource = table
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
            registro()

            txt_codPac.DataBindings.Add("Text", table, "idM")
            txt_nomPac.DataBindings.Add("Text", table, "mascotas")
            cbox_tipoPac.DataBindings.Add("Text", table, "tipo")
            txt_razPac.DataBindings.Add("Text", table, "raza")
            txt_codHist.DataBindings.Add("Text", table, "idH")
            dtp_fechAlt.DataBindings.Add("Text", table, "alta")
            dtp_fechIng.DataBindings.Add("Text", table, "ingreso")
            txt_motIng.DataBindings.Add("Text", table, "motivo")
            txt_diag.DataBindings.Add("Text", table, "diagnostico")
            txt_trat.DataBindings.Add("Text", table, "tratamiento")
            txt_dniCli.DataBindings.Add("Text", table, "dni")
            txt_nomCli.DataBindings.Add("Text", table, "nombre")
            txt_apeCli.DataBindings.Add("Text", table, "apellidos")
            txt_dirCli.DataBindings.Add("Text", table, "direccion")
            txt_telCli.DataBindings.Add("Text", table, "telefono")
            txt_ciuCli.DataBindings.Add("Text", table, "ciudad")
            txt_paisCli.DataBindings.Add("Text", table, "pais")
            txt_codPosCli.DataBindings.Add("Text", table, "codpostal")
            txt_mailCli.DataBindings.Add("Text", table, "email")
        Catch ex As Exception
            MsgBox("Error en la base de datos")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine(ex.Message.ToString)
            sw.Close()
            fs.Close()
        End Try

        Dim tipos() As String = {"Perro", "Gato", "Ave", "Reptil", "Roedor", "Otro"}

        For i = 0 To tipos.Length - 1
            cbox_tipoPac.Items.Add(tipos(i))
        Next

        Dim busquedas() As String = {"Cliente", "Mascota"}

        For i = 0 To busquedas.Length - 1
            cbox_buscar.Items.Add(busquedas(i))
        Next

    End Sub

    Private Sub txt_nomPac_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nomPac.KeyPress
        validarNombre(e, txt_nomPac)
    End Sub

    Private Sub txt_nomPac_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_nomPac.Leave
        nomPac = txt_nomPac.Text
    End Sub

    Private Sub cbox_tipoPac_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbox_tipoPac.SelectedIndexChanged
        tipoAnimal = cbox_tipoPac.SelectedItem
    End Sub

    Private Sub txt_razPac_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txt_razPac.KeyPress
        ValidarApellidos(e, txt_razPac)
    End Sub

    Private Sub txt_razPac_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_razPac.Leave
        razPac = txt_razPac.Text
    End Sub

    Private Sub dtp_fechIng_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtp_fechIng.ValueChanged
        fechIng = dtp_fechIng.Value.ToString
    End Sub

    Private Sub dtp_fechAlt_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtp_fechAlt.ValueChanged
        fechAlt = dtp_fechAlt.Value.ToString
    End Sub

    Private Sub txt_motIng_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_motIng.TextChanged
        motIng = Trim(txt_motIng.Text)
    End Sub

    Private Sub txt_diag_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_diag.TextChanged
        diag = Trim(txt_diag.Text)
    End Sub

    Private Sub txt_trat_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_trat.TextChanged
        trat = Trim(txt_trat.Text)
    End Sub

    Private Sub txt_nomCli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nomCli.KeyPress
        validarNombre(e, txt_nomCli)
    End Sub

    Private Sub txt_nomCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_nomCli.Leave
        nomCli = Trim(txt_nomCli.Text)
    End Sub

    Private Sub txt_apeCli_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txt_apeCli.KeyPress
        ValidarApellidos(e, txt_apeCli)
    End Sub

    Private Sub txt_apeCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_apeCli.Leave
        apeCli = Trim(txt_apeCli.Text)
    End Sub

    Private Sub txt_dniCli_Leave(sender As Object, e As System.EventArgs) Handles txt_dniCli.Leave
        Dim esNIF As Boolean = Verificar_DNI(txt_dniCli.Text)
        If esNIF Then
            dniCli = Trim(txt_dniCli.Text)
            ErrorProvider1.SetError(txt_dniCli, Nothing)
        Else
            ErrorProvider1.SetError(txt_dniCli, "No es un DNI")
            txt_dniCli.Focus()
        End If
    End Sub

    Private Sub txt_telCli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_telCli.KeyPress
        validarNumero(e, txt_telCli)
    End Sub

    Private Sub txt_telCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_telCli.Leave
        telCli = txt_telCli.Text
    End Sub

    Private Sub txt_mailCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_mailCli.Leave
        If Not (txt_mailCli.Text = Nothing) Then
            If ValidarCorreo(txt_mailCli.Text, txt_mailCli) Then
                ErrorProvider1.SetError(txt_mailCli, Nothing)
                mailCli = Trim(txt_mailCli.Text)
            Else
                ErrorProvider1.SetError(txt_mailCli, "Formato erroneo")
                txt_mailCli.Focus()
            End If
        End If
    End Sub

    Private Sub txt_dirCli_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_dirCli.TextChanged
        dirCli = Trim(txt_dirCli.Text)
    End Sub

    Private Sub txt_codPosCli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_codPosCli.KeyPress
        validarNumero(e, txt_codPosCli)
    End Sub

    Private Sub txt_codPosCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_codPosCli.Leave
        codPosCli = txt_codPosCli.Text
    End Sub

    Private Sub txt_ciuCli_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txt_ciuCli.KeyPress
        validarNombre(e, txt_ciuCli)
    End Sub

    Private Sub txt_ciuCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_ciuCli.Leave
        ciuCli = txt_ciuCli.Text
    End Sub

    Private Sub txt_paisCli_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txt_paisCli.KeyPress
        validarNombre(e, txt_paisCli)
    End Sub

    Private Sub txt_paisCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_paisCli.Leave
        paisCli = txt_paisCli.Text
    End Sub

    Private Sub btn_siguiente_Click(sender As System.Object, e As System.EventArgs) Handles btn_siguiente.Click
        BindingContext(table).Position += 1
        registro()
    End Sub

    Private Sub btn_anterior_Click(sender As System.Object, e As System.EventArgs) Handles btn_anterior.Click
        BindingContext(table).Position -= 1
        registro()
    End Sub

    Private Sub btn_ultimo_Click(sender As System.Object, e As System.EventArgs) Handles btn_ultimo.Click
        BindingContext(table).Position = BindingContext(table).Count - 1
        registro()
    End Sub

    Private Sub btn_inicio_Click(sender As System.Object, e As System.EventArgs) Handles btn_inicio.Click
        BindingContext(table).Position = 0
        registro()
    End Sub

    Private Sub SalirToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SalirToolStripMenuItem.Click
        Form1.Show()
        Me.Close()
    End Sub

    Private Sub CitaPreviaToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CitaPreviaToolStripMenuItem1.Click
        citaprevia.Show()
        Me.Hide()
    End Sub

    Private Sub AltasToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AltasToolStripMenuItem.Click
        limpiarCajas()
    End Sub

    Private Sub GuardarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GuardarToolStripMenuItem.Click
        guardarDatos(txt_dniCli.Text, txt_codPac.Text)
    End Sub

    Private Sub CarnetToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles CarnetToolStripMenuItem.Click
        AddHandler PrintDocument1.PrintPage, AddressOf Me.imprimir
        PrintDocument1.Print()
    End Sub

    Private Sub btn_buscar_Click(sender As System.Object, e As System.EventArgs) Handles btn_buscar.Click
        busqueda(cbox_buscar.Text)
    End Sub

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' <summary>
    ''' VALIDA QUE EL FORMATO DEL EMAIL SEA CORRECTO,
    ''' QUE CONTENGA UN @ Y UN PUNTO SEGUIDO DE ENTRE
    ''' DOS Y TRES CARACTERES.
    ''' </summary>
    ''' <param name="correo">CADENA A SER EVALUADA</param>
    ''' <param name="txtbox">EL TEXTBOS AL QUE SE DIRIGE EL ERRORPROVIDER</param>
    ''' <returns>DEVUELVE TRUE SI EL FORMATO ES CORRECTO</returns>
    ''' <remarks></remarks>
    Public Function ValidarCorreo(ByVal correo As String, ByVal txtbox As TextBox) As Boolean
        'DESPUES DE IMPORTAR: System.Text.RegularExpressions
        Dim re As New Regex("\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+")
        Dim valido As Boolean
        If re.IsMatch(correo) Then
            valido = True
            ErrorProvider1.SetError(txtbox, Nothing)
        Else
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
            valido = False
        End If
        re = Nothing
        Return valido
    End Function

    ''' <summary>
    ''' VALIDA QUE EL TEXTO INTRODUCIDO SOLO CONTENGA
    ''' LETRAS Y ESPACIOS EN BLANCO. Y QUITA LOS ESPACIOS
    ''' EN BLANCO QUE SOBRAN
    ''' </summary>
    ''' <param name="e">EVENTO DE TECLA PRECIONADA</param>
    ''' <param name="txtbox">EL TEXTBOX AL QUE SE DIRIGE EL ERRORPROVIDER</param>
    ''' <remarks></remarks>
    Public Sub ValidarApellidos(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        'QUITA ESPACIOS EN BLANCO AL PRINCIPIO Y SE ASEGURA QUE SOLO HAYA UNO EN BLANCO ENTRE PALABRAS
        If (Char.IsDigit(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsNumber(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsSymbol(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsPunctuation(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        Else
            If txtbox.Text.StartsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim
                ErrorProvider1.SetError(txtbox, Nothing)
            ElseIf txtbox.Text.EndsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim + " "
                txtbox.SelectionStart = txtbox.Text.Length
            End If
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Public Sub validarNombre(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        'SOLO ADMITE LETRAS Y ESPACIOS
        If (Char.IsDigit(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsNumber(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsSymbol(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsPunctuation(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        Else
            If txtbox.Text.StartsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim
                ErrorProvider1.SetError(txtbox, Nothing)
            ElseIf txtbox.Text.EndsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim + " "
                txtbox.SelectionStart = txtbox.Text.Length
            End If
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Private Sub validarNumero(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        If e.KeyChar < "0" Or e.KeyChar > "9" Then
            ErrorProvider1.SetError(txtbox, "Sólo admite numerales")
            If e.KeyChar <> Chr(8) Then
                e.KeyChar = Chr(0)
                ErrorProvider1.SetError(txtbox, "Sólo admite numerales")
            Else
                ErrorProvider1.SetError(txtbox, Nothing)
            End If
        Else
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Private Function CalculaLetraDNI(ByVal cadena As String) As String
        ' CALCULA LA LETRA DEL DNI Y DELVUELVE EL DNI CORRECTO
        Const myCadena As String = "TRWAGMYFPDXBNJZSQVHLCKE"
        Const myNumeros As String = "0123456789"
        Dim a, b, c, NIF As Integer
        Dim sb As New StringBuilder
        cadena = Trim(cadena)
        If Len(cadena) = 0 Then Return ""
        ' SELECCIONA SOLO LOS NUMEROS
        For i As Integer = 0 To cadena.Length - 1
            If myNumeros.IndexOf(cadena(i)) > -1 Then
                sb.Append(cadena(i))
            End If
        Next
        cadena = sb.ToString
        a = 0
        NIF = CInt(Val(cadena))
        Do
            b = CInt(Int(NIF / 24))
            c = NIF - (24 * b)
            a = a + c
            NIF = b
        Loop While b <> 0
        b = CInt(Int(a / 23))
        c = a - (23 * b)
        Return cadena & Mid(myCadena, CInt(c + 1), 1)
    End Function

    Public Function Verificar_DNI(ByVal cadena As String) As Boolean
        ' COMPRUEBA SI EL DNI ES VALIDO Y DEVUELVE TRUE SI ES CORRECTO
        Dim valido As Boolean
        Dim aux As String
        cadena = cadena.ToUpper 'CAMBIA LA LETRA A MAYUSCULAS
        aux = cadena.Substring(0, cadena.Length - 1) 'SELECCIONA SOLO LOS NUMEROS
        If aux.Length >= 7 AndAlso IsNumeric(aux) Then
            aux = CalculaLetraDNI(aux) 'CALCULA LA LETRA DEL DNI A PARTIR DE LOS NUMEROS
            If cadena <> aux Then 'COMPARA LA LETRA CALCULADA CON LA INGRESADA
                valido = False
            Else
                valido = True
            End If
        End If
        Return valido
    End Function

    Public Sub limpiarCajas()
        txt_apeCli.Clear()
        txt_ciuCli.Clear()
        txt_codHist.Clear()
        txt_codPac.Clear()
        txt_codPosCli.Clear()
        txt_diag.Clear()
        txt_dirCli.Clear()
        txt_dniCli.Clear()
        txt_mailCli.Clear()
        txt_motIng.Clear()
        txt_nomCli.Clear()
        txt_nomPac.Clear()
        txt_paisCli.Clear()
        txt_razPac.Clear()
        txt_telCli.Clear()
        txt_trat.Clear()
    End Sub

    Private Sub registro()
        lbl_cont.Text = ((Me.BindingContext(table).Position + 1).ToString + " de ") + BindingContext(table).Count.ToString
    End Sub

    Private Sub busqueda(ByVal caso As String)

        Select Case caso
            Case "Cliente"

                Dim m As Integer = 0
                Dim pos As Integer = 0
                conexion = New MySqlConnection(connectionstring)
                Dim command As New MySqlCommand("select * from cliente where apellidos=@apellidos", conexion)
                command.Parameters.AddWithValue("@apellidos", txt_apeCli.Text)
                Try
                    conexion.Open()
                    Dim res As MySqlDataReader = command.ExecuteReader
                    Do While res.Read
                        If res.Item("apellidos") = txt_apeCli.Text Then
                            txt_dniCli.Text = res("dni")
                            txt_nomCli.Text = res("nombre")
                            txt_apeCli.Text = res("apellidos")
                            txt_telCli.Text = res("telefono")
                            txt_mailCli.Text = res("email")
                            txt_dirCli.Text = res("direccion")
                            txt_ciuCli.Text = res("ciudad")
                            txt_paisCli.Text = res("pais")

                            m = 1
                        Else
                            pos = pos + 1
                        End If
                    Loop
                    If m = 0 Then
                        MsgBox("No hay coincidencias")
                    End If
                Catch ex As Exception
                    MsgBox("Hay un error")
                End Try
                conexion.Close()
            Case "Mascota"
                Dim m As Integer = 0
                Dim pos As Integer = 0
                Dim ordenSQL As New MySqlCommand("select * from mascota where nombre=@nombre", conexion)
                ordenSQL.Parameters.AddWithValue("@nombre", txt_nomPac.Text)
                conexion.Open()
                Dim res As MySqlDataReader = ordenSQL.ExecuteReader
                Try
                    Do While res.Read
                        If res.Item("nombre") = txt_nomCli.Text Then
                            txt_codPac.Text = res("id")
                            txt_nomPac.Text = res("nombre")
                            cbox_tipoPac.Text = res("tipo")
                            txt_razPac.Text = res("raza")

                            m = 1
                        Else
                            pos = pos + 1
                        End If
                    Loop
                    If m = 0 Then
                        MsgBox("No hay coincidencias")
                    End If
                Catch ex As Exception
                    MsgBox("Hay un error de búsqueda")
                    Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
                    Dim sw As New StreamWriter(fs)
                    sw.WriteLine("Hay un error de búsqueda" + ex.Message.ToString)
                    sw.Close()
                    fs.Close()
                End Try
                conexion.Close()

        End Select
    End Sub

    Private Sub imprimir(ByVal sender As Object, ByVal ev As PrintPageEventArgs)
        Try
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 144, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 152, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 160, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 168, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 176, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 184, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 192, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 200, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 208, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 216, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 224, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 232, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 240, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 248, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 256, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 264, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 272, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 280, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 288, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 296, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 302, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 312, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 320, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 328, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 336, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 344, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 352, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 360, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 368, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 376, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 384, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 392, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 400, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 408, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 416, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 424, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 432, 65)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 444, 65)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 87)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 109)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 129)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 149)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 169)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 189)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 209)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 229)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 450, 249)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 87)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 109)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 129)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 149)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 169)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 189)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 209)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 229)
            ev.Graphics.DrawString("|", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 139, 249)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 144, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 152, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 160, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 168, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 176, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 184, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 192, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 200, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 208, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 216, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 224, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 232, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 240, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 248, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 256, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 264, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 272, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 280, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 288, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 296, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 302, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 312, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 320, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 328, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 336, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 344, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 352, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 360, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 368, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 376, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 384, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 392, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 400, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 408, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 416, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 424, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 432, 250)
            ev.Graphics.DrawString("_", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 444, 250)
            ev.Graphics.DrawString("NUEVO ZOO", New Font("Calibri", 18, FontStyle.Regular), Brushes.Black, 150, 90)
            ev.Graphics.DrawString("Numero de socio: ", New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 150, 130)
            ev.Graphics.DrawString(txt_dniCli.Text, New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 300, 130)
            ev.Graphics.DrawString("Nombre: ", New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 150, 160)
            ev.Graphics.DrawString(txt_nomCli.Text, New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 300, 160)
            ev.Graphics.DrawString("Apellidos: ", New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 150, 190)
            ev.Graphics.DrawString(txt_apeCli.Text, New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 300, 190)
            ev.Graphics.DrawString("Teléfono: ", New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 150, 220)
            ev.Graphics.DrawString(txt_telCli.Text, New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 300, 220)
            ev.Graphics.DrawString("Email: ", New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 150, 250)
            ev.Graphics.DrawString(txt_mailCli.Text, New Font("Calibri", 10, FontStyle.Regular), Brushes.Black, 300, 250)
            ev.HasMorePages = False
        Catch ex As Exception
            MsgBox("Error en la base de datos")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine("Error en la base de datos" + ex.Message.ToString)
            sw.Close()
            fs.Close()
        End Try
    End Sub

    Private Function consultaSiExsisten(ByVal pk As String, ByVal tabla As String, ByVal valor As String) As Boolean
        Dim rd As MySqlDataReader
        Dim cmd As New MySqlCommand
        Dim xresult As String = Nothing
        conexion = New MySqlConnection(connectionstring)
        conexion.Open()
        cmd.CommandText = "SELECT " + pk + " FROM " + tabla + " WHERE " + pk + "=@id"
        cmd.Parameters.AddWithValue("@id", valor)
        cmd.Connection = conexion
        rd = cmd.ExecuteReader
        If rd.Read Then
            xresult = rd.GetString(0)
        End If
        conexion.Close()
        If xresult = valor Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function idMascota(ByVal idMasc As String) As String
        Dim rd As MySqlDataReader
        Dim cmd As New MySqlCommand
        Dim xresult As String = Nothing
        conexion = New MySqlConnection(connectionstring)
        conexion.Open()
        cmd.CommandText = "SELECT id FROM mascota where id=@id"
        cmd.Parameters.AddWithValue("@id", idMasc)
        cmd.Connection = conexion
        rd = cmd.ExecuteReader
        If rd.Read Then
            Return xresult = rd.GetString(0)
        End If
        conexion.Close()
        Return xresult
    End Function

    Private Sub guardarDatos(ByVal dni As String, ByVal codpac As String)
        If txt_dniCli.TextLength > 0 Then
            Dim existeCliente As Boolean = consultaSiExsisten("dni", "cliente", dni)
            Select Case existeCliente
                Case True
                    Dim exsisteMascota As Boolean = consultaSiExsisten("id", "mascota", codpac)
                    Select Case exsisteMascota
                        Case True
                            If idMascota(txt_codPac.Text) <> "NO" Then
                                guardarHistorial(idMascota(txt_codPac.Text))
                            Else
                                MsgBox("Error en integridad de la base de datos. Consulte con el servicio técnico.", vbCritical, "Error en la Base de Datos")
                            End If
                        Case False
                            guardarMascota(codPac)
                            Dim xcommandtext As String = ("SELECT MAX(id) FROM mascota")
                            Dim xmda As New MySqlDataAdapter(commandtext, connectionstring)
                            Dim xtable As New DataTable
                            xmda.Fill(xtable)
                            Dim cadena As New TextBox
                            cadena.DataBindings.Add("text", xtable, "mascota.id")
                            guardarHistorial(cadena.Text)
                    End Select
                Case False
                    guardarCliente()
                    guardarMascota(codPac)
                    Dim xcommandtext As String = ("SELECT MAX(id) FROM mascota")
                    Dim xmda As New MySqlDataAdapter(commandtext, connectionstring)
                    Dim xtable As New DataTable
                    xmda.Fill(xtable)
                    Dim cadena As New TextBox
                    cadena.DataBindings.Add("text", xtable, "mascota.id")
                    guardarHistorial(cadena.Text)
            End Select
        Else
            MsgBox("El campo de DNI de Cliente es obligatorio", vbCritical, "ERROR")
        End If
        table.Clear()
        adapter.Fill(table)
        DataGridView1.DataSource = table
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
    End Sub

    Private Sub guardarCliente()
        Try
            conexion = New MySqlConnection(connectionstring)
            Dim insertCli As MySqlCommand
            Dim insert As String = "INSERT INTO cliente( dni,nombre,apellidos, direccion,telefono,ciudad,pais,codpostal,email) Values(@dni,@nombre,@apellidos,@direccion,@telefono,@ciudad,@pais,@codpostal,@email)"
            insertCli = New MySqlCommand(insert, conexion)
            With insertCli.Parameters
                .AddWithValue("@dni", txt_dniCli.Text)
                .AddWithValue("@nombre", nomCli)
                .AddWithValue("@apellidos", apeCli)
                .AddWithValue("@direccion", dirCli)
                .AddWithValue("@telefono", telCli)
                .AddWithValue("@ciudad", ciuCli)
                .AddWithValue("@pais", paisCli)
                .AddWithValue("@codpostal", codPosCli)
                .AddWithValue("@email", mailCli)
            End With
            conexion.Open()
            insertCli.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error al guardar Clente " + ex.Message.ToString, vbCritical, "ERROR")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine("Error al guardar Clente " + ex.Message.ToString)
            sw.Close()
            fs.Close()
            conexion.Close()
            conexion.Close()
            conexion.Close()
        Finally
            conexion.Close()
        End Try
    End Sub

    Private Sub guardarMascota(ByVal pkClente As String)
        Try
            conexion = New MySqlConnection(connectionstring)
            Dim insertMas As MySqlCommand
            Dim ordenMascota As String = "INSERT INTO mascota(nombre,tipo,raza,duenio)VALUES(@nombre,@tipo,@raza,@duenio)"
            insertMas = New MySqlCommand(ordenMascota, conexion)
            insertMas.Parameters.AddWithValue("@nombre", nomPac)
            insertMas.Parameters.AddWithValue("@tipo", tipoAnimal)
            insertMas.Parameters.AddWithValue("@raza", razPac)
            insertMas.Parameters.AddWithValue("@duenio", dniCli)

            conexion.Open()
            insertMas.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox("Error al guardar Mascota " + ex.Message.ToString, vbCritical, "ERROR")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine("Error al guardar Mascota " + ex.Message.ToString)
            sw.Close()
            fs.Close()
            conexion.Close()
            conexion.Close()
        Finally
            conexion.Close()
        End Try
    End Sub

    Private Sub guardarHistorial(ByVal pkMascota As String)
        Try
            conexion = New MySqlConnection(connectionstring)
            Dim insertHist As MySqlCommand
            Dim oredenHistorial As String = "INSERT INTO historial(ingreso,alta,motivo,diagnostico,tratamiento,mascota)VALUES(@ingreso,@alta,@motivo,@diagnostico,@tratamiento,@mascota)"
            insertHist = New MySqlCommand(oredenHistorial, conexion)
            With insertHist
                Try
                    .Parameters.AddWithValue("@ingreso", dtp_fechIng.Text)
                    .Parameters.AddWithValue("@alta", dtp_fechAlt.Text)
                Catch ex As Exception
                    MsgBox("Error al guaradar FECHA historial", vbCritical, "ERROR")
                    Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
                    Dim sw As New StreamWriter(fs)
                    sw.WriteLine("Error al guaradar FECHA historial" + ex.Message.ToString)
                    sw.Close()
                    fs.Close()
                End Try
                .Parameters.AddWithValue("@motivo", motIng)
                .Parameters.AddWithValue("@diagnostico", diag)
                .Parameters.AddWithValue("@tratamiento", trat)
                .Parameters.AddWithValue("@mascota", Val(pkMascota))
            End With

            conexion.Open()
            insertHist.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox("Error al guardar Historial " + ex.Message.ToString, vbCritical, "ERROR")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine("Error al guardar Historial " + ex.Message.ToString)
            sw.Close()
            fs.Close()
            conexion.Close()
        Finally
            conexion.Close()
        End Try
    End Sub

End Class